package dao;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;



import modelo.Departamento;

/**
 *
 */

/**
 * @author Adiestramiento
 *
 */
public class DepartamentoDAO extends BaseDao{

	public DepartamentoDAO() throws ClassNotFoundException{
		super();
	}

	public void insertDepartamento(Departamento depto) throws SQLException{

		String sql ="INSERT INTO rarc1.dpto"+
					"(nombre, descripcion," +
					"ubicacion,"+
					"telefono,codgrn)"+
					"VALUES" +
					"(?,?,?,?,?)";

		PreparedStatement pst= getCon().prepareStatement(sql);
		//pst.setInt(1,depto.getIdDepto().intValue());
		pst.setString(1, depto.getNombre());
		pst.setString(2, depto.getDescripDepto());
		pst.setString(3, depto.getUbicacion());
		pst.setString(4, depto.getTelefono());
		pst.setInt(5, depto.getIdGenerecia());
		pst.execute();

	}

	public Departamento getDepartamento(Integer id) throws SQLException{
		String sql ="SELECT * FROM rarc1.dpto" +
				" d WHERE d.cod = ?";
		PreparedStatement pst= getCon().prepareStatement(sql);
		pst.setInt(1,id.intValue());

		ResultSet rows= pst.executeQuery();

		Departamento depto= null;

		if(rows.next()){
			depto= new Departamento();
			depto.setIdDepto(new Integer(rows.getInt("cod")));
			depto.setDescripDepto(rows.getString("descripcion"));
			depto.setTelefono(rows.getString("telefono"));
			depto.setUbicacion(rows.getString("ubicacion"));
			depto.setIdGenerecia(new Integer(rows.getInt("codgrn")));
			depto.setNombre(rows.getString("nombre"));

		}

		return depto;

	}


	public void DeleteDepartamento(Integer id) throws SQLException{
		String sql ="DELETE FROM RARC1.dpto" +
				" d WHERE d.cod = ?";
		PreparedStatement pst= getCon().prepareStatement(sql);
		pst.setInt(1,id.intValue());
		pst.execute();
	}

	public void UpdateDepartamento(Departamento depto) throws SQLException{

		String sql ="UPDATE rarc1.dpto SET"+
					" nombre = ? , descripcion = ?," +
					" ubicacion = ? ,"+
					" telefono = ? , codgrn = ? "+
					" WHERE cod = ?";

		PreparedStatement pst= getCon().prepareStatement(sql);
		pst.setString(1, depto.getNombre());
		pst.setString(2, depto.getDescripDepto());
		pst.setString(3, depto.getUbicacion());
		pst.setString(4, depto.getTelefono());
		pst.setInt(5, depto.getIdGenerecia());
		pst.setInt(6, depto.getIdDepto() );

		pst.execute();

	}

	public List<Departamento> getAll() throws SQLException{
		String sql ="SELECT * FROM rarc1.dpto";
		PreparedStatement pst= getCon().prepareStatement(sql);
//		pst.setInt(1,id.intValue());

		ResultSet rows= pst.executeQuery();


		List<Departamento> deps = new ArrayList<Departamento>();
		Departamento depto= null;

		while(rows.next()){
			depto= new Departamento();
			depto.setIdDepto(new Integer(rows.getInt("cod")));
			depto.setDescripDepto(rows.getString("descripcion"));
			depto.setTelefono(rows.getString("telefono"));
			depto.setUbicacion(rows.getString("ubicacion"));
			depto.setIdGenerecia(new Integer(rows.getInt("codgrn")));
			depto.setNombre(rows.getString("nombre"));
			deps.add(depto);
		}

		return deps;

	}


	public Integer getLastID() {
		String sql ="SELECT COD FROM rarc1.dpto ORDER BY COD DESC FETCH FIRST 1 ROWS ONLY";
		Integer id=0;
		try {
			PreparedStatement pst= getCon().prepareStatement(sql);
			ResultSet rows= pst.executeQuery();
			if(rows.next()){
				id= rows.getInt("cod");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			System.out.println(e);
		}
		return id;
	}

}
